package com.example.demo.demos.web.service;

import com.example.demo.demos.web.controller.ExcelController;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class ExcelService {
    private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(ExcelService.class);

    public static File processFilesWithHeaderMapping(List<File> files) throws IOException {
        // 使用 LinkedHashMap 确保列的顺序
        LinkedHashMap<String, Integer> columnMapping = new LinkedHashMap<>();
        List<Map<String, String>> dataRows = new ArrayList<>();

        // 读取第一个文件的列头顺序
        if (files.isEmpty()) {
            logger.error("文件列表不能为空");
            throw new IllegalArgumentException("文件列表不能为空");
        }

        File firstFile = files.get(0);
        int index = 1;
        try (FileInputStream fis = new FileInputStream(firstFile);
             Workbook workbook = WorkbookFactory.create(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            Row headerRow = sheet.getRow(sheet.getFirstRowNum());
            if (headerRow == null) {
                logger.error("第一个文件没有列头");
                throw new IllegalArgumentException("第一个文件没有列头");
            }

            // 读取第一个文件的列头顺序
            for (int colIndex = 0; colIndex < headerRow.getLastCellNum(); colIndex++) {
                Cell cell = headerRow.getCell(colIndex);
                String headerName = getCellValueAsString(cell).trim();
                if (colIndex == 1) {
                    headerName = "送样号";
                } else if (colIndex == 2) {
                    headerName = "分析编号";
                }
                if (!headerName.isEmpty()) {
                    columnMapping.put(headerName, colIndex);
                }
            }

            // 读取第一个文件的数据行
            for (int rowIndex = sheet.getFirstRowNum() + 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                Map<String, String> rowData = new HashMap<>();
                for (int colIndex = 0; colIndex < headerRow.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    Cell cell1 = headerRow.getCell(colIndex);
                    String headerName = getCellValueAsString(cell1).trim();
                    if (colIndex == 1) {
                        headerName = "送样号";
                    } else if (colIndex == 2) {
                        headerName = "分析编号";
                    }
                    if (!headerName.isEmpty()) {
                        String cellValue = (cell == null) ? "" : getCellValueAsString(cell);
                        if (headerName.contains("注意") && !cellValue.equals(" 序号")) {
                            cellValue = String.valueOf(index);
                            index++;
                        }
                        rowData.put(headerName, cellValue);
                        columnMapping.putIfAbsent(headerName, columnMapping.size()); // 新列头加入全局映射
                    }
                }
                dataRows.add(rowData);
            }
        }

        // 遍历剩余的文件，读取列头并合并数据

        for (int i = 1; i < files.size(); i++) {
            try (FileInputStream fis = new FileInputStream(files.get(i));
                 Workbook workbook = WorkbookFactory.create(fis)) {

                Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
                Row headerRow = sheet.getRow(sheet.getFirstRowNum());
                if (headerRow == null) continue; // 空表格直接跳过

                // 检查是否需要跳过第二行
                boolean skipSecondRow = false;
                if (sheet.getLastRowNum() >= 1) {
                    Row secondRow = sheet.getRow(1);
                    if (secondRow != null && secondRow.getCell(0) != null && "序号".equals(getCellValueAsString(secondRow.getCell(0)).trim())) {
                        skipSecondRow = true;
                    }
                }

                // 读取每行数据
                for (int rowIndex = sheet.getFirstRowNum() + 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    if (skipSecondRow && rowIndex == 1) continue; // 跳过第二行

                    Row row = sheet.getRow(rowIndex);
                    if (row == null) continue;

                    Map<String, String> rowData = new HashMap<>();
                    for (int colIndex = 0; colIndex < headerRow.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        Cell cell1 = headerRow.getCell(colIndex);
                        String headerName = getCellValueAsString(cell1).trim();
                        if (colIndex == 1) {
                            headerName = "送样号";
                        } else if (colIndex == 2) {
                            headerName = "分析编号";
                        }
                        if (!headerName.isEmpty()) {
                            String cellValue = (cell == null) ? "" : getCellValueAsString(cell);

                            if (headerName.contains("注意") && !cellValue.equals(" 序号")) {
                                cellValue = String.valueOf(index);
                                index++;
                            }
                            rowData.put(headerName, cellValue);
                            columnMapping.putIfAbsent(headerName, columnMapping.size()); // 新列头加入全局映射
                        }
                    }
                    dataRows.add(rowData);
                }
            }
        }

        // 创建汇总文件
        Workbook summaryWorkbook = new XSSFWorkbook();
        Sheet summarySheet = summaryWorkbook.createSheet("水质全分析");

        // 创建字体对象
        Font font = summaryWorkbook.createFont();
        font.setFontName("仿宋"); // 设置字体为仿宋
        font.setFontHeightInPoints((short) 10); // 设置字体大小
        font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色

        // 创建单元格样式并应用字体
        CellStyle style = summaryWorkbook.createCellStyle();
        style.setFont(font);

        // 写入列头
        Row headerRow = summarySheet.createRow(0);
        List<String> headers = new ArrayList<>(columnMapping.keySet());
        for (int colIndex = 0; colIndex < headers.size(); colIndex++) {
            Cell cell = headerRow.createCell(colIndex);
            cell.setCellValue(headers.get(colIndex));
            cell.setCellStyle(style); // 应用样式
        }

        // 写入数据
        int rowIndex = 1;
        for (Map<String, String> dataRow : dataRows) {
            Row targetRow = summarySheet.createRow(rowIndex++);
            for (int colIndex = 0; colIndex < headers.size(); colIndex++) {
                String header = headers.get(colIndex);
                // 如果缺少列，填充空值
                String value = dataRow.getOrDefault(header, "");
                Cell cell = targetRow.createCell(colIndex);
                cell.setCellValue(value);
                cell.setCellStyle(style); // 应用样式
            }
        }

        // 清除第一行的第二列和第三列的内容
        Row firstRow = summarySheet.getRow(0);
        if (firstRow != null) {
            firstRow.getCell(1).setCellValue(""); // 第二列
            firstRow.getCell(2).setCellValue(""); // 第三列
        }

        // 合并第一行的第一列到第三列
        CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, 2); // 行范围从0到0，列范围从0到2
        summarySheet.addMergedRegion(cellRange);

        // 设置合并单元格的值
        if (firstRow != null) {
            Cell mergedCell = firstRow.getCell(0);
            mergedCell.setCellValue("注意：此结果只是草稿！！！");
            mergedCell.setCellStyle(style); // 应用样式
        }

        // 保存到文件
        File summaryFile = new File("uploads/试验数据统计表.xlsx");
        try (FileOutputStream fos = new FileOutputStream(summaryFile)) {
            summaryWorkbook.write(fos);
        }

        summaryWorkbook.close();
        return summaryFile;
    }

    // 辅助方法：将单元格值转换为字符串 1
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return cell.toString();
        }
    }
}